package com.example.demo.mapper;

import com.example.demo.entity.StudentCourse;
import org.apache.ibatis.annotations.*;
import java.util.List;
import java.util.Optional;

@Mapper
public interface StudentCourseMapper {

    String COLUMNS = "id, student_id, course_id, enrollment_status, enrollment_date, completion_date, score, created_at, updated_at";

    // 1. 根据 ID 查找单个选课记录
    @Select("SELECT " + COLUMNS + " FROM student_courses WHERE id = #{id}")
    Optional<StudentCourse> findById(@Param("id") Integer id);

    // 2. 根据学生学号和课程 ID 查找记录
    @Select("SELECT " + COLUMNS + " FROM student_courses WHERE student_id = #{studentId} AND course_id = #{courseId}")
    Optional<StudentCourse> findByStudentIdAndCourseId(@Param("studentId") String studentId, @Param("courseId") String courseId);

    // 3. 查找某个学生的所有选课记录
    @Select("SELECT " + COLUMNS + " FROM student_courses WHERE student_id = #{studentId}")
    List<StudentCourse> findByStudentId(@Param("studentId") String studentId);

    // 4. 插入新的选课记录
    @Insert("INSERT INTO student_courses (student_id, course_id, enrollment_status, enrollment_date, created_at, updated_at) " +
            "VALUES (#{studentId}, #{courseId}, #{enrollmentStatus}, #{enrollmentDate}, #{createdAt}, #{updatedAt})")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    int insert(StudentCourse studentCourse);

    // 5. 更新选课记录
    @Update({
            "<script>",
            "UPDATE student_courses",
            "SET updated_at = #{updatedAt}",
            "<if test='enrollmentStatus != null'>, enrollment_status = #{enrollmentStatus}</if>",
            "<if test='score != null'>, score = #{score}</if>",
            "<if test='completionDate != null'>, completion_date = #{completionDate}</if>",
            "WHERE id = #{id}",
            "</script>"
    })
    int update(StudentCourse studentCourse);

    // 6. 删除选课记录
    @Delete("DELETE FROM student_courses WHERE id = #{id}")
    int deleteById(@Param("id") Integer id);

    // 7. 检查学生是否已选某课程
    @Select("SELECT COUNT(*) > 0 FROM student_courses WHERE student_id = #{studentId} AND course_id = #{courseId}")
    boolean existsByStudentIdAndCourseId(@Param("studentId") String studentId, @Param("courseId") String courseId);

    // 8. 根据状态查找选课记录
    @Select("SELECT " + COLUMNS + " FROM student_courses WHERE enrollment_status = #{enrollmentStatus}")
    List<StudentCourse> findByEnrollmentStatus(@Param("enrollmentStatus") String enrollmentStatus);

    // 9. 根据课程ID查找选课记录
    @Select("SELECT " + COLUMNS + " FROM student_courses WHERE course_id = #{courseId}")
    List<StudentCourse> findByCourseId(@Param("courseId") String courseId);

    @Select("SELECT " + COLUMNS + " FROM student_course WHERE student_id = #{studentId} AND course_id IN (SELECT course_id FROM course WHERE semester_id = #{semesterId})")
    List<StudentCourse> findByStudentIdAndSemester(
            @Param("studentId") String studentId,
            @Param("semesterId") String semesterId
    );

}